package cn.edu.nju.software.postgresqlmanage;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import cn.edu.nju.software.beans.CourseBean;

public class SubjectM {
	// add new courses to own course list
	public static boolean addCourses(ArrayList<CourseBean> newCourses)
			throws SQLException {

		Connection conn = PostgreSQLConnection.getConnection();
		boolean updateOk = false;

		try {
			String command = "INSERT INTO subject VALUES (?,?,?,?,?,?,?,?)";
			PreparedStatement prepareState = conn.prepareStatement(command);

			for (CourseBean courseBean : newCourses) {
				String cid = courseBean.getId();
				String cname = courseBean.getName();
				String cteacher = courseBean.getTeacher();
				String cplace = courseBean.getPlace();
				String cinstitute = courseBean.getInstitute();
				int cgreads = courseBean.getGreads();
				int cnumber = courseBean.getNumber();
				boolean cshared = courseBean.getShared();

				prepareState.setString(1, cid);
				prepareState.setString(2, cname);
				prepareState.setString(4, cinstitute);
				prepareState.setString(3, cteacher);
				prepareState.setInt(6, cgreads);
				prepareState.setInt(7, cnumber);
				prepareState.setString(5, cplace);
				prepareState.setBoolean(8, cshared);

				prepareState.executeUpdate();
			}
			updateOk = true;
		} catch (Exception exc) {
			updateOk = false;
		} finally {
			conn.close();
		}

		return updateOk;
	}

	// delete a course from own list by given course id
	public static boolean deleteCourse(ArrayList<String> courseIds)
			throws SQLException {

		Connection conn = PostgreSQLConnection.getConnection();
		boolean updateOk = false;

		try {
			String command = "DELETE FROM subject WHERE bianhao = ?";
			PreparedStatement prepareState = conn.prepareStatement(command);

			for (String cid : courseIds) {
				prepareState.setString(1, cid);

				prepareState.executeUpdate();
			}
			updateOk = true;
		} catch (Exception exc) {
			updateOk = false;
		} finally {
			conn.close();
		}

		return updateOk;
	}

	// get all courses that could be shared with others
	public static ArrayList<CourseBean> getSharedCourses() throws SQLException {

		Connection conn = PostgreSQLConnection.getConnection();
		ArrayList<CourseBean> sharedCourses = new ArrayList<CourseBean>();
		String institute = "A";
		boolean isShared = true;

		try {
			String command = "SELECT * FROM subject WHERE xueyuan = ? AND gongxiang = ?";
			PreparedStatement prepareState = conn.prepareStatement(command);
			prepareState.setString(1, institute);
			prepareState.setBoolean(2, isShared);

			ResultSet rs = prepareState.executeQuery();
			while (rs.next()) {
				String cid = rs.getString(1);
				String cname = rs.getString(2);
				String cinstitute = rs.getString(4);
				String cteacher = rs.getString(3);
				int cgreads = rs.getInt(6);
				int cnumber = rs.getInt(7);
				String cplace = rs.getString(5);
				boolean cshared = rs.getBoolean(8);

				CourseBean sharedOne = new CourseBean(cid, cname, cinstitute,
						cteacher, cgreads, cnumber, cplace, cshared);
				sharedCourses.add(sharedOne);
			}
		} catch (Exception exc) {
			
		} finally {
			conn.close();
		}

		return sharedCourses;
	}

	// get all courses for the manager
	public static ArrayList<CourseBean> getAllCourses() throws SQLException {

		Connection conn = PostgreSQLConnection.getConnection();
		ArrayList<CourseBean> allCourses = new ArrayList<CourseBean>();

		try {
			String command = "SELECT * FROM subject";
			Statement state = conn.createStatement();
			
			ResultSet rs = state.executeQuery(command);
			while(rs.next()){
				String cid = rs.getString(1);
				String cname = rs.getString(2);
				String cinstitute = rs.getString(4);
				String cteacher = rs.getString(3);
				int cgreads = rs.getInt(6);
				int cnumber = rs.getInt(7);
				String cplace = rs.getString(5);
				boolean cshared = rs.getBoolean(8);

				CourseBean getOne = new CourseBean(cid, cname, cinstitute,
						cteacher, cgreads, cnumber, cplace, cshared);
				allCourses.add(getOne);
			}
		} catch (Exception exc) {
			
		} finally {
			conn.close();
		}

		return allCourses;
	}

	// get chosen courses by given list of id
	public static ArrayList<CourseBean> getChoosedCourses(ArrayList<String> cids)
			throws SQLException {

		Connection conn = PostgreSQLConnection.getConnection();
		ArrayList<CourseBean> chosenCourses = new ArrayList<CourseBean>();

		try {
			String command = "SELECT * FROM subject WHERE bianhao = ?";
			PreparedStatement prepareState = conn.prepareStatement(command);
			
			for(String cid : cids){
				prepareState.setString(1, cid);
				ResultSet rs = prepareState.executeQuery();
				while(rs.next()){
					String cname = rs.getString(2);
					String cinstitute = rs.getString(4);
					String cteacher = rs.getString(3);
					int cgreads = rs.getInt(6);
					int cnumber = rs.getInt(7);
					String cplace = rs.getString(5);
					boolean cshared = rs.getBoolean(8);

					CourseBean getOne = new CourseBean(cid, cname, cinstitute,
							cteacher, cgreads, cnumber, cplace, cshared);
					chosenCourses.add(getOne);
				}
			}
		} catch (Exception exc) {
			
		} finally {
			conn.close();
		}

		return chosenCourses;
	}

	// get choose able courses by given chose list of id
	public static ArrayList<CourseBean> getChoosableCourses(
			ArrayList<String> cids) throws SQLException {

		Connection conn = PostgreSQLConnection.getConnection();
		ArrayList<CourseBean> choosableCourses = new ArrayList<CourseBean>();
		ArrayList<CourseBean> chosenCourses = new ArrayList<CourseBean>();
		ArrayList<CourseBean> allCourses = new ArrayList<CourseBean>();

		try {
			//get all courses first
			String command1 = "SELECT * FROM subject";
			Statement state = conn.createStatement();
			
			ResultSet rs1 = state.executeQuery(command1);
			while(rs1.next()){
				String cid = rs1.getString(1);
				String cname = rs1.getString(2);
				String cinstitute = rs1.getString(4);
				String cteacher = rs1.getString(3);
				int cgreads = rs1.getInt(6);
				int cnumber = rs1.getInt(7);
				String cplace = rs1.getString(5);
				boolean cshared = rs1.getBoolean(8);

				CourseBean getOne = new CourseBean(cid, cname, cinstitute,
						cteacher, cgreads, cnumber, cplace, cshared);
				allCourses.add(getOne);
			}
			
			//get courses that have been chosen
			String command2 = "SELECT * FROM subject WHERE bianhao = ?";
			PreparedStatement prepareState = conn.prepareStatement(command2);
			
			for(String cid : cids){
				prepareState.setString(1, cid);
				ResultSet rs2 = prepareState.executeQuery();
				while(rs2.next()){
					String cname = rs2.getString(2);
					String cinstitute = rs2.getString(4);
					String cteacher = rs2.getString(3);
					int cgreads = rs2.getInt(6);
					int cnumber = rs2.getInt(7);
					String cplace = rs2.getString(5);
					boolean cshared = rs2.getBoolean(8);

					CourseBean getOne = new CourseBean(cid, cname, cinstitute,
							cteacher, cgreads, cnumber, cplace, cshared);
					chosenCourses.add(getOne);
				}
			}
			
			//delete all that have been chose then get the choose able ones
			for(CourseBean cb : allCourses){
				boolean isChosen = false;
				for(CourseBean cb1 : chosenCourses){
					if(cb.getId() == cb1.getId()){
						isChosen = true;
						break;
					}
				}
				if(!isChosen){
					choosableCourses.add(cb);
				}
			}
		} catch (Exception exc) {
			
		} finally {
			conn.close();
		}

		return null;
	}
}
